
	DROP TABLE Employee CASCADE CONSTRAINTS;
	DROP TABLE Role CASCADE CONSTRAINTS;	
	DROP TABLE Hospital CASCADE CONSTRAINTS;
	DROP TABLE patient CASCADE CONSTRAINTS;
	DROP TABLE Appointment CASCADE CONSTRAINTS;	
	DROP TABLE schedule CASCADE CONSTRAINTS;	
	DROP TABLE diagnosis CASCADE CONSTRAINTS;	
	DROP TABLE border CASCADE CONSTRAINTS;	
	DROP TABLE drug CASCADE CONSTRAINTS;
	DROP TABLE rorderdrug CASCADE CONSTRAINTS;	
	
	DROP TABLE schedule CASCADE CONSTRAINTS;
	DROP TABLE scheduledetail CASCADE CONSTRAINTS;
	DROP TABLE timeslice CASCADE CONSTRAINTS;
	
	DROP SEQUENCE PATIENTSEQ;
	DROP SEQUENCE USERSEQ;
	DROP SEQUENCE ASSISTANCESEQ;
	DROP SEQUENCE APPTSEQUENCE;
	DROP SEQUENCE HOSPITALSEQ;
	DROP SEQUENCE DIAGNOSISSEQ;
	DROP SEQUENCE ORDERSEQ;
	DROP SEQUENCE DRUGSEQ;
	
	--CREATE SEQUENCE
CREATE SEQUENCE PATIENTSEQ
	MINVALUE 1 
	MAXVALUE 99999 
 	START WITH     1
 	INCREMENT BY   1
 	NOCACHE
 	NOCYCLE;
	
CREATE SEQUENCE USERSEQ
	MINVALUE 1 
	MAXVALUE 99999 
 	START WITH     1
 	INCREMENT BY   1
 	NOCACHE
 	NOCYCLE;
	
CREATE SEQUENCE ASSISTANCESEQ
	MINVALUE 1 
	MAXVALUE 99999 
 	START WITH     1
 	INCREMENT BY   1
 	NOCACHE
 	NOCYCLE;
	
CREATE SEQUENCE APPTSEQUENCE
	MINVALUE 1 
	MAXVALUE 99999 
 	START WITH     1
 	INCREMENT BY   1
 	NOCACHE
 	NOCYCLE;
	
CREATE SEQUENCE HOSPITALSEQ
	MINVALUE 32 
	MAXVALUE 99999 
 	START WITH     33
 	INCREMENT BY   1
 	NOCACHE
 	NOCYCLE;

CREATE SEQUENCE DIAGNOSISSEQ
	MINVALUE 1 
	MAXVALUE 99999 
 	START WITH     1
 	INCREMENT BY   1
 	NOCACHE
 	NOCYCLE;
	
CREATE SEQUENCE ORDERSEQ
	MINVALUE 1 
	MAXVALUE 99999 
 	START WITH     1
 	INCREMENT BY   1
 	NOCACHE
 	NOCYCLE;

 CREATE SEQUENCE DRUGSEQ
	MINVALUE 1 
	MAXVALUE 99999 
 	START WITH     1
 	INCREMENT BY   1
 	NOCACHE
 	NOCYCLE;	
 	
	--Create Role Table
	CREATE TABLE Role
	(
		RoleID number(5), 
		RoleName varchar2(40),
		CONSTRAINT Role_pk PRIMARY KEY (RoleID)
	);
	
	--Create Hospital Table
	CREATE TABLE Hospital
	(
		HospitalID number(5),
		HospitalName varchar2(40),
		address varchar2(100),
		zcode varchar2(20),
		phone varchar(20),
		fax varchar2(20), 
		email varchar2(40), 
		CONSTRAINT Hospital_pk PRIMARY KEY (HospitalID)
	);	
	
		
	--Create Employee Table 
	CREATE TABLE Employee 
	(
		EmployeeID varchar(40),
		password varchar2(40),
		lastName varchar2(40),
		firstName varchar2(40),
		birthday date,
		Sex varchar2(2),
		phone varchar2(20),
		email varchar2(40),
		address varchar2(80), 
		hospitalid number(5),
		RoleID number(5),
		CONSTRAINT Employee_pk PRIMARY KEY (EmployeeID),
		CONSTRAINT Employee_fk1 FOREIGN KEY(RoleID) REFERENCES Role(RoleID),
		CONSTRAINT Employee_fk2 FOREIGN KEY(hospitalid) REFERENCES Hospital(HospitalID)
	);

	--Create Patient Table
	create table patient
	(
		patientID varchar(40),
		password varchar2(40),
		lastName varchar2(40),
		firstName varchar2(40),
		birthday date,
		Sex varchar2(2),
		phone varchar2(20),
		email varchar2(40),
		address varchar2(80), 
		constraint patinet_pk primary key(patientID)
	);
		
	--Create Appointment Table
	CREATE TABLE Appointment 
	(
		apptID number(5),
		apptDate date,
		doctorID varchar(40),
		patientID varchar(40),	
		hostpitalID number(5),
		constraint appt_pk primary key(apptID),
		constraint doctorid_fk1 foreign key(doctorID) references employee(EmployeeID),
		constraint patientid_fk2 foreign key(patientID) references patient(patientID),
		constraint hostpitalid_fk3 foreign key(hostpitalID) references Hospital(HospitalID)
	);
	
	--CREATE SCHEDULE TABLE
	create table schedule
	(
		scheduleID number(5),
		doctorID varchar(40),
		hospitalID number(5),
		workdate date,
		availableTM number(1),
		isAvailable number(1),
		constraint schedule_pk primary key(scheduleID),
		constraint doctorID1_fk1 foreign key(doctorID) references employee(EmployeeID)
	);
	
	--CREATE scheduledetail TABLE
	create table scheduledetail
	(
		scheduleID number(5),
		hospitalID number(5),					
		workdate date,
		sliceid number(5),
		doctorID varchar(40),
		patientID varchar(40),
		isAvailable number(1),
		constraint scheduledetail_pk primary key(scheduleID)
	);	
		
	--CREATE timeslice TABLE
	create table timeslice
	(
		hospitalID number(5),
		sliceid number(5),
		slicevalue varchar(20),
		halfday number(1),
		constraint timeslice_pk primary key(hospitalID,sliceid)
	);
	
	--create diagnosis table
	create table diagnosis
	(
		diagnosisID number(5),
		apptID number(5),
		comments varchar2(100),
		constraint dia_pk primary key(diagnosisID),
		constraint diagnosis_apptid_fk foreign key(apptID) references Appointment(apptID)
	);

	--create order table
	create table border
	(
		orderID number(5),
		apptID number(5),
		constraint orderID_pk primary key(orderID),
		constraint order_apptid_fk foreign key(apptID) references Appointment(apptID)
	);
	
	--create drug table
	create table drug
	(
		drugID number(5),
		name varchar2(40),
		comments varchar2(100),
		constraint drugID_pk primary key(drugID)
	);
	
	--create rorderdrug table
	create table rorderdrug 
	(
		orderID number(5), 
		drugID number(5),
		constraint rorderdrug_pk primary key(orderID,drugID)
	);
	
	--Insert Records Into Table timeslice	
	INSERT INTO timeslice VALUES(1,1,'08:30 - 09:00', 0);
	INSERT INTO timeslice VALUES(1,2,'09:00 - 09:30', 0);	
	INSERT INTO timeslice VALUES(1,3,'09:30 - 10:00', 0);
	INSERT INTO timeslice VALUES(1,4,'10:00 - 10:30', 0);
	INSERT INTO timeslice VALUES(1,5,'10:30 - 11:00', 0);
	INSERT INTO timeslice VALUES(1,6,'11:00 - 11:30', 0);
	INSERT INTO timeslice VALUES(1,7,'11:30 - 12:00', 0);
	INSERT INTO timeslice VALUES(1,8,'13:30 - 14:00', 1);
	INSERT INTO timeslice VALUES(1,9,'14:00 - 14:30', 1);
	INSERT INTO timeslice VALUES(1,10,'14:30 - 15:00', 1);
	INSERT INTO timeslice VALUES(1,11,'15:00 - 15:30', 1);
	INSERT INTO timeslice VALUES(1,12,'15:30 - 16:00', 1);
	INSERT INTO timeslice VALUES(1,13,'16:00 - 16:30', 1);
	INSERT INTO timeslice VALUES(4,1,'08:30 - 09:00', 0);
	INSERT INTO timeslice VALUES(4,2,'09:00 - 09:30', 0);	
	INSERT INTO timeslice VALUES(4,3,'09:30 - 10:00', 0);
	INSERT INTO timeslice VALUES(4,4,'10:00 - 10:30', 0);
	INSERT INTO timeslice VALUES(4,5,'10:30 - 11:00', 0);
	INSERT INTO timeslice VALUES(4,6,'11:00 - 11:30', 0);
	INSERT INTO timeslice VALUES(4,7,'11:30 - 12:00', 0);
	INSERT INTO timeslice VALUES(4,8,'13:30 - 14:00', 1);
	INSERT INTO timeslice VALUES(4,9,'14:00 - 14:30', 1);
	INSERT INTO timeslice VALUES(4,10,'14:30 - 15:00', 1);
	INSERT INTO timeslice VALUES(4,11,'15:00 - 15:30', 1);
	INSERT INTO timeslice VALUES(4,12,'15:30 - 16:00', 1);
	INSERT INTO timeslice VALUES(4,13,'16:00 - 16:30', 1);	
	
--Insert Records Into Table usersequence	
--	INSERT INTO usersequence VALUES(1,1);
--	INSERT INTO usersequence VALUES(2,1);	
--	INSERT INTO usersequence VALUES(3,1);
--	INSERT INTO usersequence VALUES(4,1);		
--	INSERT INTO usersequence VALUES(5,1);	
	
	--Insert Records Into Table Hospital
	
INSERT INTO Hospital VALUES(1,'Albert Prevost Hospita', '6555 Gouin O, Montreal, QC,  Canada', 'H4J1C5', '514-338-4227', '', '');
INSERT INTO Hospital VALUES(2,'Catherine Booth Hospital', '4375 ave Montclair, Montreal, QC, Canada', 'H4B2K5', '514-481-0431', '', '');
INSERT INTO Hospital VALUES(3,'Cite de la Sante', '1755 Rene Laennec, Laval, QC, Canada', 'H7M3L9', '450-668-1010', '', '');
INSERT INTO Hospital VALUES(4,'Douglas Hospital', '6875 boul Lasalle, Verdun, QC, Canada', 'H4H1R3', '514-761-6131', '', '');
INSERT INTO Hospital VALUES(5,'Fleury Hospital', '2180 Fleury Est, Montreal, QC, Canada', 'H2B1K3', '514-384-2000', '', '');
INSERT INTO Hospital VALUES(6,'Guy Laporte Hospital Centre', '3590 Rue Saint-Urbain, Montreal, QC, Canada', 'H2X2N7', '514-282-5000', '', '');
INSERT INTO Hospital VALUES(7,'Hotel Dieu de Montreal', '3840 Rue St-Urbain, Montreal, QC, Canada', 'H2W1T8', '514-890-8000', '', '');
INSERT INTO Hospital VALUES(8,'Jean-Talon Hospital', '1385 Rue Jean Talon Est, Montreal, QC, Canada', 'H2E1S6', '514-495-6767', '', '');
INSERT INTO Hospital VALUES(9,'Jewish General Hospital', '3755 chemin de la cote-Sainte-Catherine, Montreal, QC, Canada', 'H3T1E2', '514-340-8222', '', '');
INSERT INTO Hospital VALUES(10,'Julius Richardson Convalescent Hospital', '5425 av Bessborough, Montreal, QC, Canada', 'H4V2S7', '514-484-7878', '', '');
INSERT INTO Hospital VALUES(11,'Lachine General Hospital', '3320 rue Notre-Dame, Lachine, QC, Canada', 'H8S3N5', '514-637-1161', '', '');
INSERT INTO Hospital VALUES(12,'Lakeshore General Hospital', '160 Stillview Ave, Point Claire, QC, Canada', 'H9R2Y2', '514-630-2225', '', '');
INSERT INTO Hospital VALUES(13,'Lasalle General Hospital', '8585 Terasse Champlain, LaSalle, QC, Canada', 'H8P1C1', '514-362-8000', '', '');
INSERT INTO Hospital VALUES(14,'Lindsay Convalescent Hospital', '6363 Chemin Hudson, Montreal, QC, Canada', 'H3S1M9', '514-731-3661', '', '');
INSERT INTO Hospital VALUES(15,'Maimonides Geriatric Centre', '5690 boul Cavendish, Cote-Saint-Luc, QC, Canada', 'H4W1W3', '514-483-2121', '', '');
INSERT INTO Hospital VALUES(16,'Maisonneuve-Rosemont Hospital', '5345 boul de Assomption, Montreal, QC, Canada', 'H1T4B3', '514-252-3400', '', '');
INSERT INTO Hospital VALUES(17,'Marie Clarac Hospital', '3530 boul Gouin E, Montreal-North, QC, Canada', 'H1H1B7', '514-321-8800', '', '');
INSERT INTO Hospital VALUES(18,'McGill University Health Centre', '1650 Ave Cedar, Montreal, QC, Canada', 'H3G1A4', '514-934-1934', '', '');
INSERT INTO Hospital VALUES(19,'Montreal Chest Institute', '3650 rue Saint-Urbain, Montreal, QC, Canada', 'H2X2P4', '514-849-5201', '', '');
INSERT INTO Hospital VALUES(20,'Montreal Children Hospital', '2300 rue Tupper, Montreal, QC, Canada', 'H3H1P3', '514-412-4400', '', '');	
	
	--Insert Records Into Table Role
	INSERT INTO Role VALUES(1,'Administrator');
	INSERT INTO Role VALUES(2,'Medical Officer');	
	INSERT INTO Role VALUES(3,'Doctor');
	INSERT INTO Role VALUES(4,'Assistant');		
	INSERT INTO Role VALUES(5,'Patient');		
	
	--Insert Records Into Table Employee
	INSERT INTO Employee VALUES('administrator','administrator','Hu','Le','01-JUL-14','01','514-1234 5678','Le@vanier.com','1234,rue decarie,montreal,qc,canada',1,1);	
	INSERT INTO Employee VALUES('doctor123','doctor','John','Smith','03-MAY-10','01','438-555 6897','smith@vanier.com','3000, street saint-croix, montreal,qc,canada',4,3);	
	INSERT INTO Employee VALUES('doctor124','doctor','Jim','Bob','03-MAY-10','01','438-666 1234','Bob@vanier.com','1250, street saint-croix, montreal,qc,canada',4,3);	
	INSERT INTO Employee VALUES('doctor125','doctor','Sidney','Smith','03-MAY-10','01','514-282-5000','Sidney@vanier.com','2547, street saint-croix, montreal,qc,canada',4,3);	
	INSERT INTO Employee VALUES('doctor126','doctor','Anne','Smith','03-MAY-10','01','514-521-5000','Anne@vanier.com','2547, street saint-croix, montreal,qc,canada',4,3);	
		
	--select lpad ( apptseq.nextval, 5, '0' ) id from dual;	
	
	
	-- Insert Records Into Table schedule	
	INSERT INTO schedule VALUES(1,'doctor123',4, '28-JUL-14', 2, 1);
	INSERT INTO schedule VALUES(2,'doctor124',4, '28-JUL-14', 1, 1);
	INSERT INTO schedule VALUES(3,'doctor124',4, '29-JUL-14', 2, 1);
	INSERT INTO schedule VALUES(4,'doctor124',4, '30-JUL-14', 2, 1);
	INSERT INTO schedule VALUES(5,'doctor125',4, '28-JUL-14', 1, 1);
	INSERT INTO schedule VALUES(6,'doctor125',4, '29-JUL-14', 2, 1);
	INSERT INTO schedule VALUES(7,'doctor126',4, '28-JUL-14', 2, 1);
		
	-- Insert Records Into Table scheduledetail			
	INSERT INTO scheduledetail VALUES(1,4,'28-Jul-14', 1, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(2,4,'28-Jul-14', 2, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(3,4,'28-Jul-14', 3, 'doctor123','PAT00001',0);
	INSERT INTO scheduledetail VALUES(4,4,'28-Jul-14', 4, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(5,4,'28-Jul-14', 5, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(6,4,'28-Jul-14', 6, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(7,4,'28-Jul-14', 7, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(8,4,'28-Jul-14', 8, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(9,4,'28-Jul-14', 9, 'doctor123','PAT00002',0);
	INSERT INTO scheduledetail VALUES(10,4,'28-Jul-14', 10, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(11,4,'28-Jul-14', 11, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(12,4,'28-Jul-14', 12, 'doctor123','',1);
	INSERT INTO scheduledetail VALUES(13,4,'28-Jul-14', 13, 'doctor123','',1);
	
	COMMIT;
